﻿Imports System.Data.SqlServerCe
Imports AvianWaves.AvianPlay.Language
Imports AvianWaves.AvianPlay.ErrorLog

Public Class AvianPlayDB
  Implements IDisposable

  Private _connection As SqlCeConnection = Nothing
  Private _command As SqlCeCommand = Nothing

  ''' <summary>
  ''' When using this class, if you use GetFileDBData and include the image, you must remember
  ''' to clean up the image on your own!  To do this, call AlbumArt.dispose and then set it to nothing.
  ''' The compact framework sometimes has issues dealing with bitmaps that are not appropriately manually
  ''' cleaned.  For some reason the garbage collector is not agressive enough, leading to out of memory
  ''' errors.
  ''' </summary>
  Public Class AudioFileTable
    Implements IDisposable

    Private _FilePath As String = ""
    Public Property FilePath() As String
      Get
        Return Me._FilePath
      End Get
      Set(ByVal value As String)
        Me._FilePath = Left(value, 260)
      End Set
    End Property

    Private _Artist As String = ""
    Public Property Artist() As String
      Get
        Return Me._Artist
      End Get
      Set(ByVal value As String)
        Me._Artist = Left(value, 100)
      End Set
    End Property

    Private _Title As String = ""
    Public Property Title() As String
      Get
        Return Me._Title
      End Get
      Set(ByVal value As String)
        Me._Title = Left(value, 100)
      End Set
    End Property

    Private _Album As String = ""
    Public Property Album() As String
      Get
        Return Me._Album
      End Get
      Set(ByVal value As String)
        Me._Album = Left(value, 100)
      End Set
    End Property

    Private _Genre As String = ""
    Public Property Genre() As String
      Get
        Return Me._Genre
      End Get
      Set(ByVal value As String)
        Me._Genre = Left(value, 50)
      End Set
    End Property

    Private _Format As String = ""
    Public Property Format() As String
      Get
        Return Me._Format
      End Get
      Set(ByVal value As String)
        Me._Format = Left(value, 25)
      End Set
    End Property

    Private _AlbumArtist As String = ""
    Public Property AlbumArtist() As String
      Get
        Return Me._AlbumArtist
      End Get
      Set(ByVal value As String)
        Me._AlbumArtist = Left(value, 100)
      End Set
    End Property

    Private _Copyright As String = ""
    Public Property Copyright() As String
      Get
        Return Me._Copyright
      End Get
      Set(ByVal value As String)
        Me._Copyright = Left(value, 100)
      End Set
    End Property

    Private _Bitrate As Integer = 0
    Public Property Bitrate() As Integer
      Get
        Return Me._Bitrate
      End Get
      Set(ByVal value As Integer)
        Me._Bitrate = value
      End Set
    End Property

    Private _FileSize As Integer = 0
    Public Property FileSize() As Integer
      Get
        Return Me._FileSize
      End Get
      Set(ByVal value As Integer)
        Me._FileSize = value
      End Set
    End Property

    Private _LengthSeconds As Integer = 0
    Public Property LengthSeconds() As Integer
      Get
        Return Me._LengthSeconds
      End Get
      Set(ByVal value As Integer)
        Me._LengthSeconds = value
      End Set
    End Property

    Private _AlbumArt As Bitmap = Nothing
    Public Property AlbumArt() As Bitmap
      Get
        Return Me._AlbumArt
      End Get
      Set(ByVal value As Bitmap)
        Me._AlbumArt = value
      End Set
    End Property

    Private _AlbumArtThumbnail As Bitmap = Nothing
    Public Property AlbumArtThumbnail() As Bitmap
      Get
        Return Me._AlbumArtThumbnail
      End Get
      Set(ByVal value As Bitmap)
        Me._AlbumArtThumbnail = value
      End Set
    End Property

    Private _HasAlbumArt As Boolean = False
    Public Property HasAlbumArt() As Boolean
      Get
        Return Me._HasAlbumArt
      End Get
      Set(ByVal value As Boolean)
        Me._HasAlbumArt = value
      End Set
    End Property

    Private _FileModifyDate As Date = MinimumDateTime
    Public Property FileModifyDate() As Date
      Get
        Return Me._FileModifyDate
      End Get
      Set(ByVal value As Date)
        If value < MinimumDateTime Then
          value = MinimumDateTime
        ElseIf value > MaximumDateTime Then
          value = MaximumDateTime
        End If
        Me._FileModifyDate = value
      End Set
    End Property

    Private _LastPlayPosition As Integer = 0
    Public Property LastPlayPosition() As Integer
      Get
        Return Me._LastPlayPosition
      End Get
      Set(ByVal value As Integer)
        Me._LastPlayPosition = value
      End Set
    End Property

    Private _Rating As Byte = 0
    Public Property Rating() As Byte
      Get
        Return Me._Rating
      End Get
      Set(ByVal value As Byte)
        Me._Rating = value
      End Set
    End Property

    Private _SkipForMetaPlaylist As Boolean = False
    Public Property SkipForMetaPlaylist() As Boolean
      Get
        Return Me._SkipForMetaPlaylist
      End Get
      Set(ByVal value As Boolean)
        Me._SkipForMetaPlaylist = value
      End Set
    End Property

    Private _IsPodcast As Boolean = False
    Public Property IsPodcast() As Boolean
      Get
        Return Me._IsPodcast
      End Get
      Set(ByVal value As Boolean)
        Me._IsPodcast = value
      End Set
    End Property

    Private _MD5Hash() As Byte = Nothing
    Public Property MD5Hash() As Byte()
      Get
        Return Me._MD5Hash
      End Get
      Set(ByVal value As Byte())
        Me._MD5Hash = value
      End Set
    End Property

    Private _LastFoundDate As Date = MinimumDateTime
    Public Property LastFoundDate() As Date
      Get
        Return Me._LastFoundDate
      End Get
      Set(ByVal value As Date)
        If value < MinimumDateTime Then
          value = MinimumDateTime
        ElseIf value > MaximumDateTime Then
          value = MaximumDateTime
        End If
        Me._LastFoundDate = value
      End Set
    End Property

    Private _Comments As String = ""
    Public Property Comments() As String
      Get
        Return Me._Comments
      End Get
      Set(ByVal value As String)
        Me._Comments = Left(value, 200)
      End Set
    End Property

    Private _Year As Integer = 0
    Public Property Year() As Integer
      Get
        Return Me._Year
      End Get
      Set(ByVal value As Integer)
        Me._Year = value
      End Set
    End Property

    Private _TrackNumber As Integer = 0
    Public Property TrackNumber() As Integer
      Get
        Return Me._TrackNumber
      End Get
      Set(ByVal value As Integer)
        If value >= 0 Then
          Me._TrackNumber = value
        Else
          value = 0
        End If
      End Set
    End Property

    Private _AddedDate As Date = MinimumDateTime
    Public ReadOnly Property AddedDate() As Date
      Get
        Return Me._AddedDate
      End Get
    End Property
    Friend Sub SetAddedDate(ByVal Value As Date)
      If Value < MinimumDateTime Then
        Value = MinimumDateTime
      ElseIf Value > MaximumDateTime Then
        Value = MaximumDateTime
      End If
      Me._AddedDate = Value
    End Sub

    Private _LastPlayedDate As Date = MinimumDateTime
    Public Property LastPlayedDate() As Date
      Get
        Return Me._LastPlayedDate
      End Get
      Set(ByVal value As Date)
        If value < MinimumDateTime Then
          value = MinimumDateTime
        ElseIf value > MaximumDateTime Then
          value = MaximumDateTime
        End If
        Me._LastPlayedDate = value
      End Set
    End Property

    Private _IsFavorite As Boolean = False
    Public Property IsFavorite()
      Get
        Return Me._IsFavorite
      End Get
      Set(ByVal value)
        Me._IsFavorite = value
      End Set
    End Property

#Region " IDisposable Support "
    Private disposedValue As Boolean = False    ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
      If Not Me.disposedValue Then
        If disposing Then
          If Me._AlbumArt IsNot Nothing Then
            Me._AlbumArt.Dispose()
          End If
          If Me._AlbumArtThumbnail IsNot Nothing Then
            Me._AlbumArtThumbnail.Dispose()
          End If
        End If
      End If
      Me.disposedValue = True
    End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
      ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
      Dispose(True)
      GC.SuppressFinalize(Me)
    End Sub
#End Region

  End Class

  Public Class PlaylistFileTable
    Private _Title As String = ""
    Public Property Title() As String
      Get
        Return Me._Title
      End Get
      Set(ByVal value As String)
        Me._Title = value
      End Set
    End Property

    Public ReadOnly Property FileNameWithoutExtension() As String
      Get
        Dim fn As String = Me.FileName
        Dim ePos As Integer = fn.LastIndexOf(".")

        If ePos < 0 Then
          ePos = fn.Length
        End If

        Return fn.Substring(0, ePos)

        'Dim sPos As Integer = Me._FilePath.LastIndexOf("\") + 1
        'Dim ePos As Integer = Me._FilePath.LastIndexOf(".")

        'If sPos < 0 OrElse sPos > Me._FilePath.Length - 1 Then
        '  sPos = 0
        'End If

        'If ePos < 0 OrElse ePos < sPos Then
        '  ePos = Me._FilePath.Length - 1
        'End If

        'Return Me._FilePath.Substring(sPos, ePos - sPos)
      End Get
    End Property

    Public ReadOnly Property FileName() As String
      Get
        Dim sPos As Integer = Me._FilePath.LastIndexOf("\") + 1

        If sPos < 0 OrElse sPos > Me._FilePath.Length - 1 Then
          sPos = 0
        End If

        Return Me._FilePath.Substring(sPos)
      End Get
    End Property

    Private _FilePath As String = ""
    Public Property FilePath() As String
      Get
        Return Me._FilePath
      End Get
      Set(ByVal value As String)
        Me._FilePath = Left(value, 260)
      End Set
    End Property

    Private _Format As String = ""
    Public Property Format() As String
      Get
        Return Me._Format
      End Get
      Set(ByVal value As String)
        Me._Format = Left(value, 20)
      End Set
    End Property

    Private _MD5Hash() As Byte = Nothing
    Public Property MD5Hash() As Byte()
      Get
        Return Me._MD5Hash
      End Get
      Set(ByVal value As Byte())
        Me._MD5Hash = value
      End Set
    End Property

    Private _LastFoundDate As Date = MinimumDateTime
    Public Property LastFoundDate() As Date
      Get
        Return Me._LastFoundDate
      End Get
      Set(ByVal value As Date)
        If value < MinimumDateTime Then
          value = MinimumDateTime
        ElseIf value > MaximumDateTime Then
          value = MaximumDateTime
        End If
        Me._LastFoundDate = value
      End Set
    End Property

    Private _FileModifyDate As Date = MinimumDateTime
    Public Property FileModifyDate() As Date
      Get
        Return Me._FileModifyDate
      End Get
      Set(ByVal value As Date)
        If value < MinimumDateTime Then
          value = MinimumDateTime
        ElseIf value > MaximumDateTime Then
          value = MaximumDateTime
        End If
        Me._FileModifyDate = value
      End Set
    End Property

    Private _IsFavorite As Boolean = False
    Public Property IsFavorite() As Boolean
      Get
        Return Me._IsFavorite
      End Get
      Set(ByVal value As Boolean)
        Me._IsFavorite = value
      End Set
    End Property

    Private _ContainsStreams As Boolean = False
    Public Property ContainsStreams() As Boolean
      Get
        Return Me._ContainsStreams
      End Get
      Set(ByVal value As Boolean)
        Me._ContainsStreams = value
      End Set
    End Property

    Private _ContainsFiles As Boolean = False
    Public Property ContainsFiles() As Boolean
      Get
        Return Me._ContainsFiles
      End Get
      Set(ByVal value As Boolean)
        Me._ContainsFiles = value
      End Set
    End Property
  End Class

  Public Class WhereClause
    Public Class WhereItem
      Private _parameter As String
      Private _valueStr As String
      Private _valueInt As Integer
      Private _valueIsString As Boolean = False

      Public Sub New(ByVal param As String, ByVal value As String)
        Me._parameter = param
        Me._valueStr = value
        Me._valueIsString = True
      End Sub

      Public Sub New(ByVal param As String, ByVal value As Integer)
        Me._parameter = param
        Me._valueInt = value
        Me._valueIsString = False
      End Sub

      Public Property Parameter() As String
        Get
          Return Me._parameter
        End Get
        Set(ByVal value As String)
          Me._parameter = value
        End Set
      End Property

      Public Property ValueString() As String
        Get
          Return Me._valueStr
        End Get
        Set(ByVal value As String)
          Me._valueStr = value
          Me._valueIsString = True
        End Set
      End Property

      Public Property ValueInteger() As Integer
        Get
          Return Me._valueInt
        End Get
        Set(ByVal value As Integer)
          Me._valueInt = value
          Me._valueIsString = False
        End Set
      End Property

      Public ReadOnly Property ValueIsString() As Boolean
        Get
          Return Me._valueIsString
        End Get
      End Property
    End Class

    Private _whereItems As New List(Of WhereItem)

    Public Sub Clear()
      Me._whereItems.Clear()
    End Sub

    Public ReadOnly Property WhereItems() As List(Of WhereItem)
      Get
        Return Me._whereItems
      End Get
    End Property

    Public ReadOnly Property Count()
      Get
        Return Me._whereItems.Count
      End Get
    End Property

    Public Sub AddItem(ByVal wi As WhereItem)
      Me._whereItems.Add(wi)
    End Sub

    Public ReadOnly Property SQLCEWhereClause() As String
      Get
        Dim ret As String = ""
        For i As Integer = 0 To Me._whereItems.Count - 1
          ret &= SQLCEWhereClause(i)
        Next
        Return ret
      End Get
    End Property

    Public ReadOnly Property SQLCEWhereClause(ByVal index As Integer) As String
      Get
        If index >= 0 AndAlso index < Me._whereItems.Count Then
          Return " (" & Me._whereItems(index).Parameter & " @P" & index.ToString & ") "
        Else
          Return Nothing
        End If
      End Get
    End Property

    Public ReadOnly Property SQLCEParameter() As List(Of SqlCeParameter)
      Get
        If Me._whereItems.Count > 0 Then
          Dim scpm As New List(Of SqlCeParameter)
          For i As Integer = 0 To Me._whereItems.Count - 1
            scpm.Add(SQLCEParameter(i))
          Next
          Return scpm
        Else
          Return Nothing
        End If
      End Get
    End Property

    Public ReadOnly Property SQLCEParameter(ByVal index As Integer) As SqlCeParameter
      Get
        If index >= 0 AndAlso index < Me._whereItems.Count Then
          If Me._whereItems(index).ValueIsString Then
            Dim ret As New SqlCeParameter("@P" & index.ToString, Data.SqlDbType.NVarChar)
            ret.Value = Me._whereItems(index).ValueString
            Return ret
          Else
            Dim ret As New SqlCeParameter("@P" & index.ToString, Data.SqlDbType.Int)
            ret.Value = Me._whereItems(index).ValueInteger
            Return ret
          End If
        Else
          Return Nothing
        End If
      End Get
    End Property
  End Class

  Public Class MD5FileTable
    Public FilePath As String = ""
    Public MD5Hash() As Byte = Nothing
  End Class

  Public Class PodcastSubscriptionTable
    Implements IComparable(Of PodcastSubscriptionTable)

    Private _key As Integer
    Public Property Key() As Integer
      Get
        Return Me._key
      End Get
      Set(ByVal value As Integer)
        Me._key = value
      End Set
    End Property

    Private _subscription As Integer
    Public Property Subscription() As Integer
      Get
        Return Me._subscription
      End Get
      Set(ByVal value As Integer)
        Me._subscription = value
      End Set
    End Property

    Private _filePath As String = String.Empty
    Public Property FilePath() As String
      Get
        Return Me._filePath
      End Get
      Set(ByVal value As String)
        Me._filePath = Left(value, 260)
      End Set
    End Property

    Private _downloadURL As String = String.Empty
    Public Property DownloadURL() As String
      Get
        Return Me._downloadURL
      End Get
      Set(ByVal value As String)
        Me._downloadURL = Left(value, 512)
      End Set
    End Property

    Private _downloadTime As Date = MinimumDateTime
    Public Property DownloadTime() As Date
      Get
        Return Me._downloadTime
      End Get
      Set(ByVal value As Date)
        Me._downloadTime = value
        If Me._downloadTime > MaximumDateTime Then Me._downloadTime = MaximumDateTime
        If Me._downloadTime < MinimumDateTime Then Me._downloadTime = MinimumDateTime
      End Set
    End Property

    Private _publishTime As Date = MinimumDateTime
    Public Property PublishTime() As Date
      Get
        Return Me._publishTime
      End Get
      Set(ByVal value As Date)
        Me._publishTime = value
        If Me._publishTime > MaximumDateTime Then Me._publishTime = MaximumDateTime
        If Me._publishTime < MinimumDateTime Then Me._publishTime = MinimumDateTime
      End Set
    End Property

    Public Function CompareTo(ByVal other As PodcastSubscriptionTable) As Integer Implements System.IComparable(Of PodcastSubscriptionTable).CompareTo
      Dim comp As Integer = Me.PublishTime.CompareTo(other.PublishTime)
      Return 0 - comp ' Do this because we want to sort descending
    End Function
  End Class

  Public Shared ReadOnly Property MinimumDateTime() As Date
    Get
      Return New Date(1900, 1, 1, 0, 0, 0)
    End Get
  End Property

  Public Shared ReadOnly Property MaximumDateTime() As Date
    Get
      Return New Date(9999, 1, 1, 0, 0, 0)
    End Get
  End Property

  Public Sub New()
    Try
      Dim filePath As String = Settings.DatabasePath
      If IO.File.Exists(filePath) Then
        ' Make sure the file is not read-only
        Dim attr As New IO.FileInfo(filePath)
        If attr.Attributes And IO.FileAttributes.ReadOnly = IO.FileAttributes.ReadOnly Then
          attr.Attributes = attr.Attributes And Not IO.FileAttributes.ReadOnly
        End If

        ' Open the SQL database
        Me._connection = New SqlCeConnection("Data Source=" & filePath)
        Me._connection.Open()
        Me._command = Me._connection.CreateCommand

        If Me._command Is Nothing Then
          Throw New Exception(GetLanguageString("DBCommandFailure"))
        End If

        ' Check DB version
        Try
          Me._command.CommandText = "SELECT [Value] FROM [Metadata] WHERE [Key] = 'DatabaseVersion'"
          Using result = Me._command.ExecuteReader
            If result.Read() Then
              Dim ver As Integer = Integer.Parse(result.GetString(0))
              If ver <> 1 Then
                Throw New Exception("DatabaseVersion: " & ver)
              End If
            Else
              Throw New Exception("Row not found (DatabaseVersion): ")
            End If
          End Using
        Catch ex As Exception
          Throw New Exception(GetLanguageString("DBVersionMismatch"), ex)
        End Try
      Else
        Throw New Exception(GetLanguageString("DBFileNotFound") & " " & filePath)
      End If
    Catch ex As Threading.ThreadAbortException
      ' Safe to ignore -- this happens when a thread is killed while the DB is being opened
    Catch ex As MissingMethodException
      If IO.File.Exists(Settings.SQLCECab) Then
        If MessageBox.Show(GetLanguageString("SQLCENotInstalled_InstallCE"), "Avian Play", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then
          Dim p As New Process()
          p.StartInfo.FileName = Settings.SQLCECab
          p.Start()
        End If

        Throw New TerminateWithoutReportException()
      Else
        Throw New Exception(GetLanguageString("SQLCENotInstalled"), ex)
      End If
    Catch ex As Exception
      Throw New Exception(GetLanguageString("UnableToOpenSQLCEDB") & " " & ex.Message, ex)
    End Try

    ' If we get here, instantiation is successful, so delete the SQL CE cab
    If IO.File.Exists(Settings.SQLCECab) Then
      Try
        IO.File.Delete(Settings.SQLCECab)
      Catch ex As Exception
        ' Log an error, but otherwise ignore ALL exceptions here, because, frankly, we don't care
        ErrorLog.WriteToErrorLog("Unable to delete SQL CE Cab: " & ex.ToString)
      End Try
    End If
  End Sub


  Public ReadOnly Property GetAudioFileDBData(ByVal where As WhereClause, ByVal orderby As String, Optional ByVal includeAlbumArt As Boolean = False, Optional ByVal includeMD5 As Boolean = False, Optional ByVal top As Integer = 0) As List(Of AudioFileTable)
    Get
      Dim lft As New List(Of AudioFileTable)
      Dim count As Integer = 0

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [Artist], [Title], [Album], [Genre], [Format], [AlbumArtist], [Copyright], [Bitrate], [FileSize], [LengthSeconds], [FileModifyDate], [LastPlayPosition], [Rating], [SkipForMetaPlaylist], [IsPodcast], [LastFoundDate], [Comments], [Year], [TrackNumber], [HasAlbumArt], [AddedDate], [LastPlayedDate], [IsFavorite]"
      If includeAlbumArt Then Me._command.CommandText &= ", [AlbumArt], [AlbumArtThumbnail]"
      If includeMD5 Then Me._command.CommandText &= ", [MD5Hash]"
      Me._command.CommandText &= " FROM [FileLibrary] WHERE ([NotFound] = 0) "
      If where IsNot Nothing Then
        For i As Integer = 0 To where.Count - 1
          Me._command.CommandText &= " AND " & where.SQLCEWhereClause(i)
          Me._command.Parameters.Add(where.SQLCEParameter(i))
        Next
      End If

      If orderby.Length > 0 Then Me._command.CommandText &= " ORDER BY " & orderby

      Using result = Me._command.ExecuteReader
        While result.Read AndAlso (top <= 0 OrElse count < top)
          Dim ft = New AudioFileTable

          ft.AlbumArt = Nothing
          ft.AlbumArtThumbnail = Nothing
          ft.MD5Hash = Nothing

          Me.FillAudioFileFields(ft, result)

          lft.Add(ft)

          count += 1
        End While
      End Using

      Return lft
    End Get
  End Property

  Public ReadOnly Property GetAudioFileDBData(ByVal filepath As String, Optional ByVal includeAlbumArt As Boolean = False, Optional ByVal includeMD5 As Boolean = False) As AudioFileTable
    Get
      Dim ft As AudioFileTable = Nothing

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [Artist], [Title], [Album], [Genre], [Format], [AlbumArtist], [Copyright], [Bitrate], [FileSize], [LengthSeconds], [FileModifyDate], [LastPlayPosition], [Rating], [SkipForMetaPlaylist], [IsPodcast], [LastFoundDate], [Comments], [Year], [TrackNumber], [HasAlbumArt], [AddedDate], [LastPlayedDate], [IsFavorite]"
      If includeAlbumArt Then Me._command.CommandText &= ", [AlbumArt], [AlbumArtThumbnail]"
      If includeMD5 Then Me._command.CommandText &= ", [MD5Hash]"
      Me._command.CommandText &= " FROM [FileLibrary] WHERE (FilePath = @FilePath) AND (NotFound = 0)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount > 0 Then
          ft = New AudioFileTable

          ft.AlbumArt = Nothing
          ft.AlbumArtThumbnail = Nothing
          ft.MD5Hash = Nothing

          Me.FillAudioFileFields(ft, result)
        End If
      End Using

      Return ft
    End Get
  End Property

  Public ReadOnly Property GetPlaylistFileDBData(ByVal where As WhereClause, ByVal orderby As String, Optional ByVal includeMD5 As Boolean = False, Optional ByVal top As Integer = 0) As List(Of PlaylistFileTable)
    Get
      Dim lft As New List(Of PlaylistFileTable)
      Dim count As Integer = 0

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [Title], [Format], [FileModifyDate], [LastFoundDate], [IsFavorite], [ContainsStreams], [ContainsFiles]"
      If includeMD5 Then Me._command.CommandText &= ", [MD5Hash]"
      Me._command.CommandText &= " FROM [PlaylistLibrary] WHERE ([NotFound] = 0) "
      If where IsNot Nothing Then
        For i As Integer = 0 To where.Count - 1
          Me._command.CommandText &= " AND " & where.SQLCEWhereClause(i)
          Me._command.Parameters.Add(where.SQLCEParameter(i))
        Next
      End If

      If orderby.Length > 0 Then Me._command.CommandText &= " ORDER BY " & orderby

      Using result = Me._command.ExecuteReader
        While result.Read AndAlso (top <= 0 OrElse count < top)
          Dim ft = New PlaylistFileTable

          ft.MD5Hash = Nothing

          Me.FillPlaylistFileFields(ft, result)

          lft.Add(ft)

          count += 1
        End While
      End Using

      Return lft
    End Get
  End Property

  Public Property IsAudioFileFavorite(ByVal filepath As String) As Boolean
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [IsFavorite] FROM [FileLibrary] WHERE [FilePath] = @FilePath AND [NotFound] = 0"
      Me._command.Parameters.Add("FilePath", filepath)
      Using result = Me._command.ExecuteReader
        If result.Read Then
          Return result.GetBoolean(0)
        End If
      End Using

      Return False
    End Get
    Set(ByVal value As Boolean)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "Update [FileLibrary] Set [IsFavorite] = @IsFavorite WHERE [FilePath] = @FilePath AND [NotFound] = 0"
      Me._command.Parameters.Add("FilePath", filepath)
      Me._command.Parameters.Add("IsFavorite", value)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public Property IsPlaylistFavorite(ByVal filepath As String) As Boolean
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [IsFavorite] FROM [PlaylistLibrary] WHERE [FilePath] = @FilePath AND [NotFound] = 0"
      Me._command.Parameters.Add("FilePath", filepath)
      Using result = Me._command.ExecuteReader
        If result.Read Then
          Return result.GetBoolean(0)
        End If
      End Using

      Return False
    End Get
    Set(ByVal value As Boolean)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "Update [PlaylistLibrary] Set [IsFavorite] = @IsFavorite WHERE [FilePath] = @FilePath AND [NotFound] = 0"
      Me._command.Parameters.Add("FilePath", filepath)
      Me._command.Parameters.Add("IsFavorite", value)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public ReadOnly Property GetPlaylistFileDBData(ByVal filepath As String, Optional ByVal includeMD5 As Boolean = False) As PlaylistFileTable
    Get
      Dim ft As PlaylistFileTable = Nothing

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [Title], [Format], [FileModifyDate], [LastFoundDate], [IsFavorite], [ContainsStreams], [ContainsFiles]"
      If includeMD5 Then Me._command.CommandText &= ", [MD5Hash]"
      Me._command.CommandText &= " FROM [PlaylistLibrary] WHERE (FilePath = @FilePath) AND (NotFound = 0)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount > 0 Then
          ft = New PlaylistFileTable

          ft.MD5Hash = Nothing

          Me.FillPlaylistFileFields(ft, result)
        End If
      End Using

      Return ft
    End Get
  End Property

  Private Sub FillAudioFileFields(ByRef ft As AudioFileTable, ByVal result As SqlCeDataReader)
    For i As Integer = 0 To result.FieldCount - 1
      If Not result.IsDBNull(i) Then
        Select Case result.GetName(i).Trim.ToUpper
          Case "FILEPATH"
            ft.FilePath = result.GetString(i)
          Case "ARTIST"
            ft.Artist = result.GetString(i)
          Case "TITLE"
            ft.Title = result.GetString(i)
          Case "ALBUM"
            ft.Album = result.GetString(i)
          Case "GENRE"
            ft.Genre = result.GetString(i)
          Case "FORMAT"
            ft.Format = result.GetString(i)
          Case "ALBUMARTIST"
            ft.AlbumArtist = result.GetString(i)
          Case "COPYRIGHT"
            ft.Copyright = result.GetString(i)
          Case "BITRATE"
            ft.Bitrate = result.GetInt32(i)
          Case "FILESIZE"
            ft.FileSize = result.GetInt32(i)
          Case "LENGTHSECONDS"
            ft.LengthSeconds = result.GetInt32(i)
          Case "ALBUMART"
            Dim len As Long = result.GetBytes(i, 0, Nothing, 0, 0)
            Dim b(len) As Byte
            result.GetBytes(i, 0, b, 0, len)
            ft.AlbumArt = AvianPlayDB.ConvertByteArrayToBitmap(b)
          Case "ALBUMARTTHUMBNAIL"
            Dim len As Long = result.GetBytes(i, 0, Nothing, 0, 0)
            Dim b(len) As Byte
            result.GetBytes(i, 0, b, 0, len)
            ft.AlbumArtThumbnail = AvianPlayDB.ConvertByteArrayToBitmap(b)
          Case "HASALBUMART"
            ft.HasAlbumArt = result.GetBoolean(i)
          Case "FILEMODIFYDATE"
            ft.FileModifyDate = result.GetDateTime(i)
          Case "LASTPLAYPOSITION"
            ft.LastPlayPosition = result.GetInt32(i)
          Case "RATING"
            ft.Rating = result.GetByte(i)
          Case "SKIPFORMETAPLAYLSIT"
            ft.SkipForMetaPlaylist = result.GetBoolean(i)
          Case "ISPODCAST"
            ft.IsPodcast = result.GetBoolean(i)
          Case "MD5HASH"
            Dim len As Long = result.GetBytes(i, 0, Nothing, 0, 0)
            Dim b(len) As Byte
            result.GetBytes(i, 0, b, 0, len)
            ft.MD5Hash = b
          Case "LASTFOUNDDATE"
            ft.LastFoundDate = result.GetDateTime(i)
          Case "ADDEDDATE"
            ft.SetAddedDate(result.GetDateTime(i))
          Case "LASTPLAYEDDATE"
            ft.LastPlayedDate = result.GetDateTime(i)
          Case "COMMENTS"
            ft.Comments = result.GetString(i)
          Case "YEAR"
            ft.Year = result.GetInt32(i)
          Case "TRACKNUMBER"
            ft.TrackNumber = result.GetInt32(i)
          Case "ISFAVORITE"
            ft.IsFavorite = result.GetBoolean(i)
        End Select
      End If
    Next
  End Sub

  Private Sub FillPlaylistFileFields(ByRef ft As PlaylistFileTable, ByVal result As SqlCeDataReader)
    For i As Integer = 0 To result.FieldCount - 1
      If Not result.IsDBNull(i) Then
        Select Case result.GetName(i).Trim.ToUpper
          Case "TITLE"
            ft.Title = result.GetString(i)
          Case "FILEPATH"
            ft.FilePath = result.GetString(i)
          Case "FORMAT"
            ft.Format = result.GetString(i)
          Case "FILEMODIFYDATE"
            ft.FileModifyDate = result.GetDateTime(i)
          Case "MD5HASH"
            Dim len As Long = result.GetBytes(i, 0, Nothing, 0, 0)
            Dim b(len) As Byte
            result.GetBytes(i, 0, b, 0, len)
            ft.MD5Hash = b
          Case "LASTFOUNDDATE"
            ft.LastFoundDate = result.GetDateTime(i)
          Case "CONTAINSFILES"
            ft.ContainsFiles = result.GetBoolean(i)
          Case "CONTAINSSTREAMS"
            ft.ContainsStreams = result.GetBoolean(i)
          Case "ISFAVORITE"
            ft.IsFavorite = result.GetBoolean(i)
        End Select
      End If
    Next
  End Sub

  Public ReadOnly Property GetAudioFileAlbumAndTrackNumber(ByVal filepath As String) As AudioFileTable
    Get
      Dim ft As AudioFileTable = Nothing

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [Album], [TrackNumber] FROM [FileLibrary] WHERE (FilePath = @FilePath) AND (NotFound = 0)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount > 0 Then
          ft = New AudioFileTable

          ft.AlbumArt = Nothing
          ft.AlbumArtThumbnail = Nothing
          ft.MD5Hash = Nothing

          Me.FillAudioFileFields(ft, result)
        End If
      End Using

      Return ft
    End Get
  End Property

  ''' <summary>
  ''' Attempts to rename a file in the database.  Returns any SQL CE exception (such as primary key violation) if the operation fails; returns empty string on success.
  ''' </summary>
  Public Function RenameFileDatabaseEntry(ByVal oldName As String, ByVal newName As String) As String
    RenameFileDatabaseEntry = ""
    Try
      Dim dropRow As Boolean = False

      ' Check to see if the new path exists
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath] FROM [FileLibrary] WHERE ([FilePath] = @NewPath)"
      Me._command.Parameters.Add("@NewPath", newName)
      Using rdr As SqlCeDataReader = Me._command.ExecuteReader()
        If rdr.Read Then
          ' New row already exists, drop this new row before renaming
          dropRow = True
        End If
      End Using

      If dropRow Then
        Me._command.Parameters.Clear()
        Me._command.CommandText = "DELETE FROM [FileLibrary] WHERE ([FilePath] = @NewPath)"
        Me._command.Parameters.Add("@NewPath", newName)
        Me._command.ExecuteNonQuery()
      End If

      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [FileLibrary] SET [FilePath] = @NewPath WHERE ([FilePath] = @OldPath)"
      Me._command.Parameters.Add("@OldPath", oldName)
      Me._command.Parameters.Add("@NewPath", newName)
      Me._command.ExecuteNonQuery()
    Catch ex As SqlCeException
      RenameFileDatabaseEntry = ex.ToString
    End Try
  End Function

  Public Function RenamePlaylistDatabaseEntry(ByVal oldName As String, ByVal newName As String) As String
    RenamePlaylistDatabaseEntry = ""
    Try
      Dim dropRow As Boolean = False

      ' Check to see if the new path exists
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath] FROM [PlaylistLibrary] WHERE ([FilePath] = @NewPath)"
      Me._command.Parameters.Add("@NewPath", newName)
      Using rdr As SqlCeDataReader = Me._command.ExecuteReader()
        If rdr.Read Then
          ' New row already exists, drop this new row before renaming
          dropRow = True
        End If
      End Using

      If dropRow Then
        Me._command.Parameters.Clear()
        Me._command.CommandText = "DELETE FROM [PlaylistLibrary] WHERE ([FilePath] = @NewPath)"
        Me._command.Parameters.Add("@NewPath", newName)
        Me._command.ExecuteNonQuery()
      End If

      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [PlaylistLibrary] SET [FilePath] = @NewPath WHERE ([FilePath] = @OldPath)"
      Me._command.Parameters.Add("@OldPath", oldName)
      Me._command.Parameters.Add("@NewPath", newName)
      Me._command.ExecuteNonQuery()
    Catch ex As SqlCeException
      RenamePlaylistDatabaseEntry = ex.ToString
    End Try
  End Function


  Public ReadOnly Property GetNotFoundMD5AudioFileHashes() As List(Of MD5FileTable)
    Get
      GetNotFoundMD5AudioFileHashes = New List(Of MD5FileTable)

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [MD5Hash]"
      Me._command.CommandText &= " FROM [FileLibrary] WHERE (NotFound = 1)"

      Using result = Me._command.ExecuteReader
        While result.Read() AndAlso result.FieldCount > 0
          Dim mft As New MD5FileTable

          mft.FilePath = result.GetString(0)

          Dim len As Long = result.GetBytes(1, 0, Nothing, 0, 0)
          Dim b(len) As Byte
          result.GetBytes(1, 0, b, 0, len)
          mft.MD5Hash = b

          GetNotFoundMD5AudioFileHashes.Add(mft)
        End While
      End Using
    End Get
  End Property

  Public ReadOnly Property GetNotFoundMD5PlaylistFileHashes() As List(Of MD5FileTable)
    Get
      GetNotFoundMD5PlaylistFileHashes = New List(Of MD5FileTable)

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath], [MD5Hash]"
      Me._command.CommandText &= " FROM [PlaylistLibrary] WHERE (NotFound = 1)"

      Using result = Me._command.ExecuteReader
        While result.Read() AndAlso result.FieldCount > 0
          Dim mft As New MD5FileTable

          mft.FilePath = result.GetString(0)

          Dim len As Long = result.GetBytes(1, 0, Nothing, 0, 0)
          Dim b(len) As Byte
          result.GetBytes(1, 0, b, 0, len)
          mft.MD5Hash = b

          GetNotFoundMD5PlaylistFileHashes.Add(mft)
        End While
      End Using
    End Get
  End Property

  Public ReadOnly Property GetAlbumArtLargeByFilePath(ByVal filepath As String) As Bitmap
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [AlbumArt] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 1 AndAlso Not result.IsDBNull(0) Then
          Dim len As Long = result.GetBytes(0, 0, Nothing, 0, 0)
          Dim b(len) As Byte
          result.GetBytes(0, 0, b, 0, len)
          Return AvianPlayDB.ConvertByteArrayToBitmap(b)
        End If
      End Using

      Return Nothing
    End Get
  End Property

  Public ReadOnly Property GetAlbumArtThumbnailByFilePath(ByVal filepath As String) As Bitmap
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [AlbumArtThumbnail] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 1 AndAlso Not result.IsDBNull(0) Then
          Dim len As Long = result.GetBytes(0, 0, Nothing, 0, 0)
          Dim b(len) As Byte
          result.GetBytes(0, 0, b, 0, len)
          Return AvianPlayDB.ConvertByteArrayToBitmap(b)
        End If
      End Using

      Return Nothing
    End Get
  End Property

  Public ReadOnly Property GetAllFilePaths() As List(Of String)
    Get
      Dim fps As New List(Of String)

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FilePath] FROM FileLibrary"
      Using result = Me._command.ExecuteReader
        While result.Read
          fps.Add(result.GetString(0))
        End While
      End Using

      Return fps
    End Get
  End Property

  ''' <summary>
  ''' Set the "filenotfound" flag
  ''' </summary>
  ''' <param name="filepath">The file you are setting</param>
  ''' <value>TRUE if the file is not found; FALSE if it is found</value>
  Public WriteOnly Property SetAudioFileNotFoundStatus(ByVal filepath As String) As Boolean
    Set(ByVal value As Boolean)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [FileLibrary] SET [NotFound] = @NotFound WHERE ([FilePath] = @FilePath)"
      Me._command.Parameters.Add("@NotFound", value)
      Me._command.Parameters.Add("@FilePath", filepath)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public WriteOnly Property SetPlaylistFileNotFoundStatus(ByVal filepath As String) As Boolean
    Set(ByVal value As Boolean)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [PlaylistLibrary] SET [NotFound] = @NotFound WHERE ([FilePath] = @FilePath)"
      Me._command.Parameters.Add("@NotFound", value)
      Me._command.Parameters.Add("@FilePath", filepath)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public Sub UpdateAllFileNotFoundStatus()
    Me._command.Parameters.Clear()
    Using cmd2 As SqlCeCommand = Me._connection.CreateCommand
      cmd2.CommandText = "SELECT [FilePath] FROM FileLibrary"
      Using result = cmd2.ExecuteReader
        While result.Read
          If result.FieldCount = 1 Then
            Dim fp As String = result.GetString(0)
            Me.SetAudioFileNotFoundStatus(fp) = Not AvianPlay.Library.Monitor.IsFileInScanPathAndExists(fp, Settings.LibraryScanPaths, Settings.LibraryExcludePaths)
          End If
        End While
      End Using

      cmd2.CommandText = "SELECT [FilePath] FROM PlaylistLibrary"
      Using result = cmd2.ExecuteReader
        While result.Read
          If result.FieldCount = 1 Then
            Dim fp As String = result.GetString(0)
            Me.SetPlaylistFileNotFoundStatus(fp) = Not AvianPlay.Library.Monitor.IsFileInScanPathAndExists(fp, Settings.LibraryScanPaths, Settings.LibraryExcludePaths)
          End If
        End While
      End Using
    End Using
  End Sub

  Public WriteOnly Property SetAudioFileLastFoundDate(ByVal filepath As String) As Date
    Set(ByVal value As Date)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [FileLibrary] SET [LastFoundDate] = @LastFoundDate WHERE (([FilePath] = @FilePath))"
      Me._command.Parameters.Add("@LastFoundDate", value)
      Me._command.Parameters.Add("@FilePath", filepath)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public WriteOnly Property SetPlaylistFileLastFoundDate(ByVal filepath As String) As Date
    Set(ByVal value As Date)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [PlaylistLibrary] SET [LastFoundDate] = @LastFoundDate WHERE (([FilePath] = @FilePath))"
      Me._command.Parameters.Add("@LastFoundDate", value)
      Me._command.Parameters.Add("@FilePath", filepath)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public WriteOnly Property SetAudioFileModifyDate(ByVal filepath As String) As Date
    Set(ByVal value As Date)
      Me._command.Parameters.Clear()
      Me._command.CommandText = "UPDATE [FileLibrary] SET [FileModifyDate] = @FileModifyDate WHERE (([FilePath] = @FilePath))"
      Me._command.Parameters.Add("@FileModifyDate", value)
      Me._command.Parameters.Add("@FilePath", filepath)
      Me._command.ExecuteNonQuery()
    End Set
  End Property

  Public Sub InsertAudioFileDBData(ByVal ft As AudioFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "INSERT INTO FileLibrary (FilePath, Artist, Title, Album, Genre, Format, AlbumArtist, Copyright, Bitrate, FileSize, LengthSeconds, FileModifyDate, LastPlayPosition, Rating, SkipForMetaPlaylist, IsPodcast, MD5Hash, LastFoundDate, Comments, Year, NotFound, TrackNumber, AlbumArt, AlbumArtThumbnail, HasAlbumArt, AddedDate, LastPlayedDate, IsFavorite) VALUES (@FilePath, @Artist, @Title, @Album, @Genre, @Format, @AlbumArtist, @Copyright, @Bitrate, @FileSize, @LengthSeconds, @FileModifyDate, @LastPlayPosition, @Rating, @SkipForMetaPlaylist, @IsPodcast, @MD5Hash, @LastFoundDate, @Comments, @Year, 0, @TrackNumber, @AlbumArt, @AlbumArtThumbnail, @HasAlbumArt, @AddedDate, @LastPlayedDate, @IsFavorite)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Artist", ft.Artist)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Album", ft.Album)
    Me._command.Parameters.Add("@Genre", ft.Genre)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@AlbumArtist", ft.AlbumArtist)
    Me._command.Parameters.Add("@Copyright", ft.Copyright)
    Me._command.Parameters.Add("@Bitrate", ft.Bitrate)
    Me._command.Parameters.Add("@FileSize", ft.FileSize)
    Me._command.Parameters.Add("@LengthSeconds", ft.LengthSeconds)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@LastPlayPosition", ft.LastPlayPosition)
    Me._command.Parameters.Add("@Rating", ft.Rating)
    Me._command.Parameters.Add("@SkipForMetaPlaylist", ft.SkipForMetaPlaylist)
    Me._command.Parameters.Add("@IsPodcast", ft.IsPodcast)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@AddedDate", Date.Now)
    Me._command.Parameters.Add("@LastPlayedDate", ft.LastPlayedDate)
    Me._command.Parameters.Add("@Comments", ft.Comments)
    Me._command.Parameters.Add("@Year", ft.Year)
    Me._command.Parameters.Add("@TrackNumber", ft.TrackNumber)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    If ft.MD5Hash Is Nothing Then
      Me._command.Parameters.Add("@MD5Hash", DBNull.Value)
    Else
      Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    End If
    If ft.AlbumArt Is Nothing AndAlso ft.AlbumArtThumbnail Is Nothing Then
      Me._command.Parameters.Add("@AlbumArt", Data.SqlDbType.Image).Value = DBNull.Value
      Me._command.Parameters.Add("@AlbumArtThumbnail", Data.SqlDbType.Image).Value = DBNull.Value
      Me._command.Parameters.Add("@HasAlbumArt", False)
    Else
      Me._command.Parameters.Add("@AlbumArt", Data.SqlDbType.Image).Value = ConvertBitmapToByteArray(ft.AlbumArt)
      Me._command.Parameters.Add("@AlbumArtThumbnail", Data.SqlDbType.Image).Value = ConvertBitmapToByteArray(ft.AlbumArtThumbnail)
      Me._command.Parameters.Add("@HasAlbumArt", True)
    End If
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub InsertPlaylistFileDBData(ByVal ft As PlaylistFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "INSERT INTO PlaylistLibrary (FilePath, Title, Format, FileModifyDate, LastFoundDate, IsFavorite, ContainsFiles, ContainsStreams, MD5Hash, NotFound) VALUES (@FilePath, @Title, @Format, @FileModifyDate, @LastFoundDate, @IsFavorite, @ContainsFiles, @ContainsStreams, @MD5Hash, 0)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    Me._command.Parameters.Add("@ContainsFiles", ft.ContainsFiles)
    Me._command.Parameters.Add("@ContainsStreams", ft.ContainsStreams)
    If ft.MD5Hash Is Nothing Then
      Me._command.Parameters.Add("@MD5Hash", DBNull.Value)
    Else
      Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    End If
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub InsertAudioFileDBDataNoAlbumArt(ByVal ft As AudioFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "INSERT INTO FileLibrary (FilePath, Artist, Title, Album, Genre, Format, AlbumArtist, Copyright, Bitrate, FileSize, LengthSeconds, FileModifyDate, LastPlayPosition, Rating, SkipForMetaPlaylist, IsPodcast, MD5Hash, LastFoundDate, Comments, Year, NotFound, TrackNumber, HasAlbumArt, AddedDate, LastPlayedDate, IsFavorite) VALUES (@FilePath, @Artist, @Title, @Album, @Genre, @Format, @AlbumArtist, @Copyright, @Bitrate, @FileSize, @LengthSeconds, @FileModifyDate, @LastPlayPosition, @Rating, @SkipForMetaPlaylist, @IsPodcast, @MD5Hash, @LastFoundDate, @Comments, @Year, 0, @TrackNumber, @HasAlbumArt, @AddedDate, @LastPlayedDate, @IsFavorite)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Artist", ft.Artist)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Album", ft.Album)
    Me._command.Parameters.Add("@Genre", ft.Genre)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@AlbumArtist", ft.AlbumArtist)
    Me._command.Parameters.Add("@Copyright", ft.Copyright)
    Me._command.Parameters.Add("@Bitrate", ft.Bitrate)
    Me._command.Parameters.Add("@FileSize", ft.FileSize)
    Me._command.Parameters.Add("@LengthSeconds", ft.LengthSeconds)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@LastPlayPosition", ft.LastPlayPosition)
    Me._command.Parameters.Add("@Rating", ft.Rating)
    Me._command.Parameters.Add("@SkipForMetaPlaylist", ft.SkipForMetaPlaylist)
    Me._command.Parameters.Add("@IsPodcast", ft.IsPodcast)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@AddedDate", Date.Now)
    Me._command.Parameters.Add("@LastPlayedDate", ft.LastPlayedDate)
    Me._command.Parameters.Add("@Comments", ft.Comments)
    Me._command.Parameters.Add("@Year", ft.Year)
    Me._command.Parameters.Add("@TrackNumber", ft.TrackNumber)
    Me._command.Parameters.Add("@HasAlbumArt", False)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    If ft.MD5Hash Is Nothing Then
      Me._command.Parameters.Add("@MD5Hash", DBNull.Value)
    Else
      Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    End If
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdateAudioFileDBData(ByVal ft As AudioFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE FileLibrary SET Artist = @Artist, Title = @Title, Album = @Album, Genre = @Genre, Format = @Format, AlbumArtist = @AlbumArtist, Copyright = @Copyright, Bitrate = @Bitrate, FileSize = @FileSize, LengthSeconds = @LengthSeconds, FileModifyDate = @FileModifyDate, Rating = @Rating, IsPodcast = @IsPodcast, LastFoundDate = @LastFoundDate, Comments = @Comments, Year = @Year, TrackNumber = @TrackNumber, AlbumArt = @AlbumArt, AlbumArtThumbnail = @AlbumArtThumbnail, HasAlbumArt = @HasAlbumArt, MD5Hash = @MD5Hash, IsFavorite = @IsFavorite, NotFound = 0 WHERE (FilePath = @FilePath)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Artist", ft.Artist)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Album", ft.Album)
    Me._command.Parameters.Add("@Genre", ft.Genre)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@AlbumArtist", ft.AlbumArtist)
    Me._command.Parameters.Add("@Copyright", ft.Copyright)
    Me._command.Parameters.Add("@Bitrate", ft.Bitrate)
    Me._command.Parameters.Add("@FileSize", ft.FileSize)
    Me._command.Parameters.Add("@LengthSeconds", ft.LengthSeconds)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@Rating", ft.Rating)
    Me._command.Parameters.Add("@SkipForMetaPlaylist", ft.SkipForMetaPlaylist)
    Me._command.Parameters.Add("@IsPodcast", ft.IsPodcast)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@Comments", ft.Comments)
    Me._command.Parameters.Add("@Year", ft.Year)
    Me._command.Parameters.Add("@TrackNumber", ft.TrackNumber)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    If ft.MD5Hash Is Nothing Then
      Me._command.Parameters.Add("@MD5Hash", DBNull.Value)
    Else
      Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    End If
    If ft.AlbumArt Is Nothing AndAlso ft.AlbumArtThumbnail Is Nothing Then
      Me._command.Parameters.Add("@AlbumArt", Data.SqlDbType.Image).Value = DBNull.Value
      Me._command.Parameters.Add("@AlbumArtThumbnail", Data.SqlDbType.Image).Value = DBNull.Value
      Me._command.Parameters.Add("@HasAlbumArt", False)
    Else
      Me._command.Parameters.Add("@AlbumArt", Data.SqlDbType.Image).Value = ConvertBitmapToByteArray(ft.AlbumArt)
      Me._command.Parameters.Add("@AlbumArtThumbnail", Data.SqlDbType.Image).Value = ConvertBitmapToByteArray(ft.AlbumArtThumbnail)
      Me._command.Parameters.Add("@HasAlbumArt", True)
    End If
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdateAudioFileDBDataNoAlbumArt(ByVal ft As AudioFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE FileLibrary SET Artist = @Artist, Title = @Title, Album = @Album, Genre = @Genre, Format = @Format, AlbumArtist = @AlbumArtist, Copyright = @Copyright, Bitrate = @Bitrate, FileSize = @FileSize, LengthSeconds = @LengthSeconds, FileModifyDate = @FileModifyDate, Rating = @Rating, IsPodcast = @IsPodcast, MD5Hash = @MD5Hash, LastFoundDate = @LastFoundDate, Comments = @Comments, Year = @Year, TrackNumber = @TrackNumber, IsFavorite = @IsFavorite, HasAlbumArt = 0, NotFound = 0 WHERE (FilePath = @FilePath)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Artist", ft.Artist)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Album", ft.Album)
    Me._command.Parameters.Add("@Genre", ft.Genre)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@AlbumArtist", ft.AlbumArtist)
    Me._command.Parameters.Add("@Copyright", ft.Copyright)
    Me._command.Parameters.Add("@Bitrate", ft.Bitrate)
    Me._command.Parameters.Add("@FileSize", ft.FileSize)
    Me._command.Parameters.Add("@LengthSeconds", ft.LengthSeconds)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@Rating", ft.Rating)
    Me._command.Parameters.Add("@SkipForMetaPlaylist", ft.SkipForMetaPlaylist)
    Me._command.Parameters.Add("@IsPodcast", ft.IsPodcast)
    Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@Comments", ft.Comments)
    Me._command.Parameters.Add("@Year", ft.Year)
    Me._command.Parameters.Add("@TrackNumber", ft.TrackNumber)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdatePlaylistFileDBData(ByVal ft As PlaylistFileTable)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE PlaylistLibrary SET FilePath = @FilePath, Title = @Title, Format = @Format, FileModifyDate = @FileModifyDate, LastFoundDate = @LastFoundDate, IsFavorite = @IsFavorite, ContainsFiles = @ContainsFiles, ContainsStreams = @ContainsStreams, MD5Hash = @MD5Hash, NotFound = 0 WHERE (FilePath = @FilePath)"
    Me._command.Parameters.Add("@FilePath", ft.FilePath)
    Me._command.Parameters.Add("@Title", ft.Title)
    Me._command.Parameters.Add("@Format", ft.Format)
    Me._command.Parameters.Add("@FileModifyDate", ft.FileModifyDate)
    Me._command.Parameters.Add("@LastFoundDate", ft.LastFoundDate)
    Me._command.Parameters.Add("@IsFavorite", ft.IsFavorite)
    Me._command.Parameters.Add("@ContainsFiles", ft.ContainsFiles)
    Me._command.Parameters.Add("@ContainsStreams", ft.ContainsStreams)
    If ft.MD5Hash Is Nothing Then
      Me._command.Parameters.Add("@MD5Hash", DBNull.Value)
    Else
      Me._command.Parameters.Add("@MD5Hash", ft.MD5Hash)
    End If
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub DeleteFileDBNotFoundOlderThan(ByVal dt As Date)
    ' Audio
    Me._command.Parameters.Clear()
    Me._command.CommandText = "DELETE FROM FileLibrary WHERE (LastFoundDate < @DeleteOlderEntriesThanDate) AND (NotFound = 1)"
    Me._command.Parameters.Add("@DeleteOlderEntriesThanDate", dt)
    Me._command.ExecuteNonQuery()

    ' Playlists
    Me._command.Parameters.Clear()
    Me._command.CommandText = "DELETE FROM PlaylistLibrary WHERE (LastFoundDate < @DeleteOlderEntriesThanDate) AND (NotFound = 1)"
    Me._command.Parameters.Add("@DeleteOlderEntriesThanDate", dt)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdateAudioFileDBFilePath(ByVal oldpath As String, ByVal newpath As String)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE FileLibrary SET FilePath = @NewFilePath WHERE (FilePath = @OldFilePath)"
    Me._command.Parameters.Add("@NewFilePath", newpath)
    Me._command.Parameters.Add("@OldFilePath", oldpath)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdatePlaylistFileDBFilePath(ByVal oldpath As String, ByVal newpath As String)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE PlaylistLibrary SET FilePath = @NewFilePath WHERE (FilePath = @OldFilePath)"
    Me._command.Parameters.Add("@NewFilePath", newpath)
    Me._command.Parameters.Add("@OldFilePath", oldpath)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdatePlaylistFileDBFilePath(ByVal oldpath As String, ByVal newpath As String, ByVal newtitle As String)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE PlaylistLibrary SET FilePath = @NewFilePath, Title = @Title WHERE (FilePath = @OldFilePath)"
    Me._command.Parameters.Add("@Title", newtitle)
    Me._command.Parameters.Add("@NewFilePath", newpath)
    Me._command.Parameters.Add("@OldFilePath", oldpath)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdateLastPlayPosition(ByVal path As String, ByVal position As Integer)
    If position < 0 Then position = 0
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE [FileLibrary] SET [LastPlayPosition] = @Position WHERE (FilePath = @FilePath)"
    Me._command.Parameters.Add("@FilePath", path)
    Me._command.Parameters.Add("@Position", position)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub UpdateLastPlayedDateToNow(ByVal path As String)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE [FileLibrary] SET [LastPlayedDate] = @LastPlayedDate WHERE (FilePath = @FilePath)"
    Me._command.Parameters.Add("@FilePath", path)
    Me._command.Parameters.Add("@LastPlayedDate", Date.Now)
    Me._command.ExecuteNonQuery()
  End Sub

  Public ReadOnly Property GetLastPlayPosition(ByVal path As String) As Integer
    Get
      GetLastPlayPosition = 0
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [LastPlayPosition] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", path)
      Dim result = Me._command.ExecuteScalar()
      If result IsNot Nothing Then
        Try
          GetLastPlayPosition = result
        Catch ex As Exception
          WriteToErrorLog("GetLastPlayPosition :: Query Failed" & vbCrLf & ex.ToString)
        End Try
      End If
    End Get
  End Property

  Public ReadOnly Property GetID3V1GenreByIndex(ByVal index As Integer) As String
    Get
      GetID3V1GenreByIndex = ""
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [Genre] FROM [ID3V1Genre] WHERE [Index] = @Index"
      Me._command.Parameters.Add("@Index", index)
      Dim result = Me._command.ExecuteScalar()
      If result IsNot Nothing Then
        Try
          GetID3V1GenreByIndex = result.ToString
        Catch ex As Exception
          WriteToErrorLog("GetID3V1GenreByIndex :: Query Failed" & vbCrLf & ex.ToString)
        End Try
      End If
    End Get
  End Property

  Public ReadOnly Property IsFilePodcast(ByVal filepath As String) As Boolean
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [IsPodcast] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 1 Then
          Return result.GetBoolean(0)
        End If
      End Using

      Return False
    End Get
  End Property

  Public ReadOnly Property IsFilePodcastTristate(ByVal filepath As String) As Boolean?
    Get
      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [IsPodcast] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 1 Then
          Return result.GetBoolean(0)
        End If
      End Using

      Return Nothing
    End Get
  End Property

  ''' <summary>
  ''' Get the last modification date for the file
  ''' </summary>
  ''' <param name="filepath">Path of the file</param>
  ''' <param name="includeNotFoundFiles">Optional: do we include 'not found' files in the search?  If the file is found, it will return
  ''' 1 tick over minvalue.  This is NOT minvalue (means file not found), but will always cause an update action.</param>
  ''' <value></value>
  ''' <returns>minvalue = no file in db, minvalue + 1 ticks = not found file is found in db, or date of last modified found file</returns>
  Public ReadOnly Property GetAudioFileLastModifyDate(ByVal filepath As String, Optional ByVal includeNotFoundFiles As Boolean = False) As Date
    Get
      GetAudioFileLastModifyDate = Date.MinValue

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FileModifyDate], [NotFound] FROM [FileLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 2 Then
          If result.GetBoolean(1) Then
            If includeNotFoundFiles Then
              GetAudioFileLastModifyDate = New Date(Date.MinValue.Ticks + 1)
            End If
          Else
            GetAudioFileLastModifyDate = result.GetDateTime(0)
          End If
        End If
      End Using
    End Get
  End Property

  ''' <summary>
  ''' Get the last modification date for the file
  ''' </summary>
  ''' <param name="filepath">Path of the file</param>
  ''' <param name="includeNotFoundFiles">Optional: do we include 'not found' files in the search?  If the file is found, it will return
  ''' 1 tick over minvalue.  This is NOT minvalue (means file not found), but will always cause an update action.</param>
  ''' <value></value>
  ''' <returns>minvalue = no file in db, minvalue + 1 ticks = not found file is found in db, or date of last modified found file</returns>
  Public ReadOnly Property GetPlaylistFileLastModifyDate(ByVal filepath As String, Optional ByVal includeNotFoundFiles As Boolean = False) As Date
    Get
      GetPlaylistFileLastModifyDate = Date.MinValue

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [FileModifyDate], [NotFound] FROM [PlaylistLibrary] WHERE (FilePath = @FilePath)"
      Me._command.Parameters.Add("@FilePath", filepath)

      Using result = Me._command.ExecuteReader
        If result.Read() AndAlso result.FieldCount = 2 Then
          If result.GetBoolean(1) Then
            If includeNotFoundFiles Then
              GetPlaylistFileLastModifyDate = New Date(Date.MinValue.Ticks + 1)
            End If
          Else
            GetPlaylistFileLastModifyDate = result.GetDateTime(0)
          End If
        End If
      End Using
    End Get
  End Property

  ''' <summary>
  ''' This method is used to set all music files to MinimumDateTime so that during the next full scan, every files appears to be updated and 
  ''' the data is written over in the database.
  ''' </summary>
  Public Sub SetAllAudioFilesToMinimumModifyDate()
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE [FileLibrary] SET [FileModifyDate] = @FileModifyDate"
    Me._command.Parameters.Add("@FileModifyDate", MinimumDateTime)
    Me._command.ExecuteNonQuery()
  End Sub

  ''' <summary>
  ''' This method is used to set all playlist files to MinimumDateTime so that during the next full scan, every files appears to be updated and 
  ''' the data is written over in the database.
  ''' </summary>
  Public Sub SetAllPlaylistFilesToMinimumModifyDate()
    Me._command.Parameters.Clear()
    Me._command.CommandText = "UPDATE [PlaylistLibrary] SET [FileModifyDate] = @FileModifyDate"
    Me._command.Parameters.Add("@FileModifyDate", MinimumDateTime)
    Me._command.ExecuteNonQuery()
  End Sub





  Public ReadOnly Property GetAllSubscriptionPodcastFiles() As List(Of PodcastSubscriptionTable)
    Get
      GetAllSubscriptionPodcastFiles = New List(Of PodcastSubscriptionTable)

      Me._command.Parameters.Clear()
      Me._command.CommandText = "SELECT [Key], [FilePath], [DownloadTime], [DownloadURL], [PublishTime], [Subscription] FROM [PodcastSubscriptions] ORDER BY [PublishTime] DESC"

      Using result = Me._command.ExecuteReader
        While result.Read() AndAlso result.FieldCount = 6
          Dim pst As New PodcastSubscriptionTable
          pst.Key = result.GetInt32(0)
          pst.FilePath = result.GetString(1)
          pst.DownloadTime = result.GetDateTime(2)
          pst.DownloadURL = result.GetString(3)
          pst.PublishTime = result.GetDateTime(4)
          pst.Subscription = result.GetInt32(5)
          GetAllSubscriptionPodcastFiles.Add(pst)
        End While
      End Using
    End Get
  End Property

  Public Sub RemoveSubscriptionPodcastFile(ByVal key As Integer)
    Me._command.Parameters.Clear()
    Me._command.CommandText = "DELETE FROM [PodcastSubscriptions] WHERE ([Key] = @Key)"
    Me._command.Parameters.Add("@Key", key)
    Me._command.ExecuteNonQuery()
  End Sub

  Public Sub RemoveSubscriptionPodcastFile(ByVal pst As PodcastSubscriptionTable)
    If pst IsNot Nothing Then
      Me.RemoveSubscriptionPodcastFile(pst.Key)
    End If
  End Sub

  Public Sub InsertSubscriptionPodcastFile(ByRef pst As PodcastSubscriptionTable)
    If pst IsNot Nothing Then
      Me._command.Parameters.Clear()
      Me._command.CommandText = "INSERT INTO PodcastSubscriptions ([FilePath], [DownloadTime], [DownloadURL], [PublishTime], [Subscription]) VALUES (@FilePath, @DownloadTime, @DownloadURL, @PublishTime, @Subscription)"
      Me._command.Parameters.Add("@FilePath", pst.FilePath)
      Me._command.Parameters.Add("@DownloadTime", pst.DownloadTime)
      Me._command.Parameters.Add("@DownloadURL", pst.DownloadURL)
      Me._command.Parameters.Add("@PublishTime", pst.PublishTime)
      Me._command.Parameters.Add("@Subscription", pst.Subscription)
      Me._command.ExecuteNonQuery()
    End If
  End Sub

  Public Sub UpdateSubscriptionPodcastFile(ByRef pst As PodcastSubscriptionTable)
    If pst IsNot Nothing Then
      Me._command.Parameters.Clear()
      Me._command.CommandText = "Update PodcastSubscriptions Set [FilePath]=@FilePath, [DownloadTime]=@DownloadTime, [DownloadURL]=@DownloadURL, [PublishTime]=@PublishTime, [Subscription]=@Subscription) WHERE [Key]=@Key"
      Me._command.Parameters.Add("@Key", pst.Key)
      Me._command.Parameters.Add("@FilePath", pst.FilePath)
      Me._command.Parameters.Add("@DownloadURL", pst.DownloadURL)
      Me._command.Parameters.Add("@DownloadTime", pst.DownloadTime)
      Me._command.Parameters.Add("@PublishTime", pst.PublishTime)
      Me._command.Parameters.Add("@Subscription", pst.Subscription)
      Me._command.ExecuteNonQuery()
    End If
  End Sub





  ''' <summary>
  ''' Get a new command object to use for ad hoc queries.  You MUST close this object when done.
  ''' </summary>
  Public Function CreateAdHocCommand() As System.Data.SqlServerCe.SqlCeCommand
    Return Me._connection.CreateCommand
  End Function

  ''' <summary>
  ''' Gets the current SqlCeCommand used for internal queries.  WHATEVER YOU DO, DO NOT CLOSE THIS COMMAND OBJECT!
  ''' </summary>
  Public ReadOnly Property GetInternalCommandForAdHocCommand() As SqlCeCommand
    Get
      Me._command.Parameters.Clear()
      Return Me._command
    End Get
  End Property




  Public Shared Sub FillPlayListEntryFromFileTable(ByVal ft As AudioFileTable, ByRef ple As Playlists.AVPLPlaylistEntry)
    Dim bmp As New Bitmap(1, 1)
    Dim g As Graphics = Drawing.Graphics.FromImage(bmp)

    If ft IsNot Nothing Then
      ple.Type = Playlists.AVPLPlaylistEntry.EntryType.File
      ple.FilePath = ft.FilePath
      ple.Artist = ft.Artist
      ple.Title = ft.Title
      ple.AlbumArtist = ft.AlbumArtist
      ple.Album = ft.Album
      ple.Genre = ft.Genre
      ple.HasDBThumbnail = ft.HasAlbumArt

      ' Note: This operation is WAY too slow to do on the main thread.  Include an image with caution!
      If ft.AlbumArtThumbnail IsNot Nothing Then
        ple.Thumbnail = ft.AlbumArtThumbnail.Clone
        ft.AlbumArt.Dispose()
        ft.AlbumArt = Nothing
      End If
    End If
  End Sub

  Public Shared Sub FillPlayListEntryFromDB(ByVal filepath As String, ByVal db As AvianPlayDB, ByRef ple As Playlists.AVPLPlaylistEntry)
    FillPlayListEntryFromFileTable(db.GetAudioFileDBData(filepath), ple)
  End Sub



  Public Shared Function MD5FileHash(ByVal filepath As String) As Byte()
    Using fs As New System.IO.FileStream(filepath, IO.FileMode.Open, IO.FileAccess.Read)
      Using md5 As New System.Security.Cryptography.MD5CryptoServiceProvider
        Return md5.ComputeHash(fs)
      End Using
    End Using
  End Function

  Public Shared Function ConvertBitmapToByteArray(ByVal bmp As Bitmap) As Byte()
    If bmp Is Nothing Then
      Return Nothing
    End If
    Using stream As New System.IO.MemoryStream
      bmp.Save(stream, Imaging.ImageFormat.Jpeg)
      Dim bytes(stream.Length - 1) As Byte
      Return stream.ToArray()
    End Using
  End Function

  Public Shared Function ConvertByteArrayToBitmap(ByVal bytes() As Byte) As Bitmap
    If bytes Is Nothing Then
      Return Nothing
    End If
    Using stream As New System.IO.MemoryStream(bytes)
      Return New Bitmap(stream)
    End Using
  End Function

  Public Shared Function AreByteArraysEqual(ByVal bytes1() As Byte, ByVal bytes2() As Byte) As Boolean
    If bytes1 Is Nothing OrElse bytes2 Is Nothing Then
      Return False
    ElseIf bytes1.Length = bytes2.Length Then
      For i As Integer = 0 To bytes1.Length - 1
        If bytes1(i) <> bytes2(i) Then
          Return False
        End If
      Next
      Return True
    Else
      Return False
    End If
  End Function


#Region " IDisposable Support "
  Private disposedValue As Boolean = False    ' To detect redundant calls

  ' IDisposable
  Protected Overridable Sub Dispose(ByVal disposing As Boolean)
    If Not Me.disposedValue Then
      If disposing Then
        If Me._command IsNot Nothing Then
          Me._command.Dispose()
          Me._command = Nothing
        End If
        If Me._connection IsNot Nothing Then
          Me._connection.Close()
          Me._connection = Nothing
        End If
      End If
    End If
    Me.disposedValue = True
  End Sub

  ' This code added by Visual Basic to correctly implement the disposable pattern.
  Public Sub Dispose() Implements IDisposable.Dispose
    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    Dispose(True)
    GC.SuppressFinalize(Me)
  End Sub
#End Region

End Class
